package com.qinyeit.codegen;


import com.qinyeit.codegen.entity.Column;
import com.qinyeit.codegen.entity.ColumnType;
import com.qinyeit.codegen.entity.Table;

import java.io.File;
import java.nio.charset.Charset;
import java.util.Calendar;
import java.util.List;

/**
 * 生成 flyway_migrate SQL 脚本
 * ClassName: GenSql <br/> 
 * Function: 功能描述 
 * 	<h3>脚本文件名固定格式:</h3>
 * 	v20150810193112419__create_table_users.sql<br/>
 * 	v + 时间戳(yyyyMMddHHmmssSSS) + __ + 备注 
 * 	<h3>备注以固定格式书写:</h3>
 * 	<ul>
 * 	<li>新建表 create_表名</li>
 *  <li>删除表 drop_表名</li>
 *  <li>修改表名 rename_旧表名_to_新表名</li>
 *  <li>添加字段 add_column_字段名_to_表名</li>
 *  <li>修改字段名 rename_column_旧字段名_to_新字段名_from_表名</li>
 *  <li>修改字段 change_column_字段名_from_表名</li>
 *  <li>删除字段 remove_column_字段名_from_表名</li>
 *  <li>添加索引 add_index_索引名_to_表名</li>
 *  <li>删除索引 remove_index_索引名_from_表名</li>
 * 	</ul>
 * <br/> 
 * date: 2015年8月10日 下午7:27:47 <br/> 
 * 
 * @author liyang
 * @version  1.0
 * @since JDK 1.7
 */
public class GenerateMigrate {
	
	private static final int COMMENT_LEN = 60;

	/**
	 * createComment <br/>
	 *
	 * @param times 时间
	 * @param tableName 表名
	 * @param handle 操作类型
	 * @since JDK 1.7
	 */
	private static String createComment(String times, String tableName, String handle){
		String result = "\n";

		result += "\n/*"+GenUtils.rightPad("", COMMENT_LEN, "-")+"*/";
		result += "\n/*"+GenUtils.rightPad("", COMMENT_LEN)+"*/";
		result += "\n/*"+GenUtils.rightPad(" Times  : "+times, COMMENT_LEN, "")+"*/";
		result += "\n/*"+GenUtils.rightPad(" Table  : "+tableName, COMMENT_LEN, "")+"*/";
		result += "\n/*"+GenUtils.rightPad(" Handle : "+handle, COMMENT_LEN, "")+"*/";
		result += "\n/*"+GenUtils.rightPad("", COMMENT_LEN)+"*/";
		result += "\n/*"+GenUtils.rightPad("", COMMENT_LEN, "-")+"*/";
		result += "\n";
		
		return result;
	}

	/**
	 * 循环创建表
	 *
	 * @param filePath 保存路径
	 * @param tables 表集合
     */
	public static void createTable(String filePath, List<Table> tables){
		for (Table table : tables) {
			createTable(filePath, table);
		}
	}
	
	/**
	 * createTable:创建表. <br/>
	 *
	 * @param filePath 文件路径
	 * @param table	表实体
	 * @since JDK 1.7
	 */
	public static void createTable(String filePath, Table table){
		String times = GenUtils.formatDate(Calendar.getInstance(), "yyyyMMddHHmmssSSS");
		StringBuffer sb = new StringBuffer();
		sb.append(createComment(times, table.getName(), "create table"));
		sb.append("\n");
		sb.append("\n");
		if(table.isDrop()){
			sb.append("DROP TABLE IF EXISTS ").append(table.getName()).append(";\n");
			sb.append("\n");
			sb.append("\n");
		}
		
		sb.append("CREATE TABLE ").append(table.getName()).append("(\n");
		
		sb.append("\tid bigint(20) NOT NULL AUTO_INCREMENT COMMENT '唯一标识，不涉及业务',\n");
		for (Column column : table.getColumns()) {
			sb.append("\t");
			sb.append(createTableColumn(column));
			sb.append("\n");
		}
		//sb.append("\tcreate_at DATETIME COMMENT '创建时间',\n");
		//sb.append("\tupdate_at DATETIME COMMENT '更改时间',\n");
		sb.append("\tPRIMARY KEY (id)\n");
		sb.append(") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='").append(table.getComment()).append("';\n");

		String fileName = String.format("v%s__create_%s.sql", times, table.getName());
		saveFiles(filePath, fileName, sb);
	}

	/**
	 * 创建列
	 * @param column 列实体
	 * @return 拼接后的字符串
     */
	public static String createTableColumn(Column column){
		String rv ;
		String notNull = column.isNotNull()?"NOT NULL":"";
		
		switch (ColumnType.valueOf(column.getColumnType())) {
		case VARCHAR:
			rv = column.getColumnName()+" "+column.getColumnType()+" ("+column.getColumnLength()+") COLLATE utf8_unicode_ci "+notNull+" COMMENT '"+column.getColumnComment()+"',";
			break;
		default:
			rv = column.getColumnName()+" "+column.getColumnType()+" COMMENT '"+column.getColumnComment()+"',";
			break;
		}
		return rv;
	}
	
	/**
	 * dropTable:删除表. <br/>
	 *
	 * @param filePath 文件路径
	 * @param tableName 表名
	 * @since JDK 1.7
	 */
	public static void dropTable(String filePath, String tableName){
		String times = GenUtils.formatDate(Calendar.getInstance(), "yyyyMMddHHmmssSSS");
		String sql = String.format("DROP TABLE IF EXISTS %s;", tableName);
		
		StringBuilder sb = new StringBuilder();
		sb.append(createComment(times, tableName, "drop table"));
		sb.append("\n\n").append(sql).append("\n\n\n");
		
		String fileName = String.format("v%s__drop_%s.sql", times, tableName);
		saveFiles(filePath, fileName, sb);
	}
	
	/**
	 * 修改字段名
	 * renameTable <br/>
	 *
	 * @param filePath		文件路径
	 * @param oldTableName	老的表名
	 * @param newTableName  新的表名
	 * @since JDK 1.7
	 */
	public static void renameTable(String filePath, String oldTableName, String newTableName){
		String times = GenUtils.formatDate(Calendar.getInstance(), "yyyyMMddHHmmssSSS");
		String sql = String.format("RENAME TABLE %s TO %s;", oldTableName, newTableName);
		
		StringBuilder sb = new StringBuilder();
		sb.append(createComment(times, oldTableName, "rename to "+newTableName));
		sb.append("\n\n").append(sql).append("\n\n\n");
		
		String fileName = String.format("v%s__rename_%s_to_%s.sql", times, oldTableName, newTableName);
		saveFiles(filePath, fileName, sb);
	}
	
	/**
	 * 添加字段
	 * addColumn. <br/>
	 *
	 * @param filePath		文件路径
	 * @param tableName		表名
	 * @param column 		列
	 * @since JDK 1.7
	 */
	public static void addColumn(String filePath, String tableName, Column column){
		String times = GenUtils.formatDate(Calendar.getInstance(), "yyyyMMddHHmmssSSS");
		String columnType = column.getColumnType();
		if(column.getColumnType().equalsIgnoreCase("varchar")){
			columnType += " ("+column.getColumnLength()+")";
		}
		String sql = String.format("ALTER TABLE %s ADD %s %s;", tableName, column.getColumnName(), columnType);
		StringBuilder sb = new StringBuilder();
		sb.append(createComment(times, tableName, "add column "+column.getColumnName()));
		sb.append("\n\n").append(sql).append("\n\n\n");
		String fileName = String.format("v%s__add_column_%s_to_%s.sql", times, column.getColumnName(), tableName);
		saveFiles(filePath, fileName, sb);
	}
	
	/**
	 * 修改字段名
	 * renameColumn. <br/>
	 *
	 * @param filePath			文件路径
	 * @param tableName			表名
	 * @param oldColumnName		老的列名
	 * @param column			列
	 * @since JDK 1.7
	 */
	public static void renameColumn(String filePath, String tableName, String oldColumnName, Column column){
		String times = GenUtils.formatDate(Calendar.getInstance(), "yyyyMMddHHmmssSSS");
		String columnType = column.getColumnType();
		if(column.getColumnType().equalsIgnoreCase("varchar")){
			columnType += " ("+column.getColumnLength()+")";
		}
		String sql = String.format("ALTER TABLE %s CHANGE %s %s %s;", tableName, oldColumnName, column.getColumnName(), columnType);
		
		StringBuilder sb = new StringBuilder();
		sb.append(createComment(times, tableName, "rename column "+oldColumnName+" to "+column.getColumnName()));
		sb.append("\n\n").append(sql).append("\n\n\n");
		String fileName = String.format("v%s__rename_column_%s_to_%s_from_%s.sql", times, oldColumnName, column.getColumnName(), tableName);
		saveFiles(filePath, fileName, sb);
	}
	
	/**
	 * 修改字段类型
	 * modifyColumn:. <br/>
	 *
	 * @param filePath		文件保存路径
	 * @param tableName		表名
	 * @param column 		列
	 * @since JDK 1.7
	 */
	public static void modifyColumn(String filePath, String tableName, Column column){
		String times = GenUtils.formatDate(Calendar.getInstance(), "yyyyMMddHHmmssSSS");
		String columnType = column.getColumnType();
		if(column.getColumnType().equalsIgnoreCase("varchar")){
			columnType += " ("+column.getColumnLength()+")";
		}
		String sql = String.format("ALTER TABLE %s MODIFY %s %s", tableName, column.getColumnName(), columnType);
		
		StringBuilder sb = new StringBuilder();
		sb.append(createComment(times, tableName, "modify column "+column.getColumnName()));
		sb.append("\n\n").append(sql).append("\n\n\n");

		String fileName = String.format("v%s__change_column_%s_from_%s.sql", times, column.getColumnName(), tableName);
		saveFiles(filePath, fileName, sb);
	}

	/**
	 * 删除字段
	 * @param filePath		文件保存路径
	 * @param tableName		表名
	 * @param columnName	列名
     */
	public static void removeColumn(String filePath, String tableName, String columnName){
		String times = GenUtils.formatDate(Calendar.getInstance(), "yyyyMMddHHmmssSSS");
		String sql = "ALTER TABLE %s DROP COLUMN %s";
		sql = String.format(sql, tableName, columnName);
		
		StringBuilder sb = new StringBuilder();
		sb.append(createComment(times, tableName, "remove column "+columnName+";"));
		sb.append("\n\n").append(sql).append(";\n\n\n");
		
		String fileName = String.format("v%s__remove_column_%s_to_%s.sql", times, columnName, tableName);
		saveFiles(filePath, fileName, sb);
	}

	/**
	 * 添加索引
	 * @param filePath 路径
	 * @param tableName 表名
	 * @param indexName 索引名称
	 * @param columnName 列名
     */
	public static void addIndex(String filePath, String tableName, String indexName, String columnName){
		String times = GenUtils.formatDate(Calendar.getInstance(), "yyyyMMddHHmmssSSS");
		String sql = String.format("ALTER TABLE %s ADD FULLTEXT INDEX %s (%s);", tableName, indexName, columnName);
		
		StringBuilder sb = new StringBuilder();
		sb.append(createComment(times, tableName, "add index "+indexName+"("+columnName+")"));
		sb.append("\n\n").append(sql).append("\n\n\n");

		String fileName = String.format("v%s__add_index_%s_to_%s.sql", times, indexName, tableName);
		saveFiles(filePath, fileName, sb);
	}
	
	/**
	 * 删除索引
	 * removeIndex:(). <br/>
	 *
	 * @param filePath	 	文件路径
	 * @param tableName		表名
	 * @param indexName 	索引
	 * @since JDK 1.7
	 */
	public static void removeIndex(String filePath, String tableName, String indexName){
		String times = GenUtils.formatDate(Calendar.getInstance(), "yyyyMMddHHmmssSSS");
		String sql = String.format("ALTER TABLE %s DROP INDEX %s;", tableName, indexName);
		
		StringBuilder sb = new StringBuilder();
		sb.append(createComment(times, tableName, "remove index "+indexName));
		sb.append("\n\n").append(sql).append("\n\n\n");
		String fileName = String.format("v%s__remove_index_%s_from_%s.sql", times, indexName, tableName);
		saveFiles(filePath, fileName, sb);
	}
	
	/**
	 * saveFiles:(保存到文件). <br/>
	 *
	 * @param filePath	保存文件路径
	 * @param fileName	保存文件的名称
	 * @param sb 		保存的内容
	 * @since JDK 1.7
	 */
	public static void saveFiles(String filePath, String fileName, Object sb){
		try {
			GenUtils.writeStringToFile(new File(filePath, fileName), sb.toString(), Charset.forName("utf-8"));
		} catch (Exception e) {
		}
	}
}
